package zy.dao.base.color.impl;

import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import zy.dao.BaseDaoImpl;
import zy.dao.base.color.ColorDAO;
import zy.entity.base.color.T_Base_Color;
import zy.entity.base.product.T_Base_Product_Color;
@Repository
public class ColorDAOImpl extends BaseDaoImpl implements ColorDAO{

	@Override
	public Integer count(Map<String, Object> param) {
		Object name = param.get("name");
		StringBuffer sql = new StringBuffer("");
		sql.append(" select count(1)");
		sql.append(" from t_base_color t");
		sql.append(" where 1 = 1");
        if(null != name && !"".equals(name)){
        	sql.append(" and instr(t.cr_name,:name)>0");
        }
		sql.append(" and t.companyid=:companyid");
		Integer count = namedParameterJdbcTemplate.queryForObject(sql.toString(), param, Integer.class);
		return count;
	}

	@Override
	public List<T_Base_Color> list(Map<String, Object> param) {
		Object name = param.get("name");
		StringBuffer sql = new StringBuffer("");
		sql.append(" select cr_id,cr_code,cr_name,cr_spell");
		sql.append(" from t_base_color t");
		sql.append(" where 1 = 1");
        if(null != name && !"".equals(name)){
        	sql.append(" and instr(t.cr_name,:name)>0");
        }
		sql.append(" and t.companyid=:companyid");
		sql.append(" order by cr_id desc");
		sql.append(" limit :start,:end");
		List<T_Base_Color> list = namedParameterJdbcTemplate.query(sql.toString(), param, new BeanPropertyRowMapper<>(T_Base_Color.class));
		return list;
	}

	@Override
	public Integer queryByName(T_Base_Color color) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" select cr_id from t_base_color");
		sql.append(" where 1=1");
		if(null != color.getCr_name() && !"".equals(color.getCr_name())){
			sql.append(" and cr_name=:cr_name");
		}
		if(null != color.getCr_id() && color.getCr_id() > 0){
			sql.append(" and cr_id <> :cr_id");
		}
		sql.append(" and companyid=:companyid");
		sql.append(" limit 1");
		try{
			Integer id = namedParameterJdbcTemplate.queryForObject(sql.toString(), 
					new BeanPropertySqlParameterSource(color),Integer.class);
			return id;
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}
	}

	@Override
	public T_Base_Color queryByID(Integer cr_id) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" select cr_id,cr_code,cr_name from t_base_color");
		sql.append(" where cr_id=:cr_id");
		sql.append(" limit 1");
		try{
			T_Base_Color data = namedParameterJdbcTemplate.queryForObject(sql.toString(), new MapSqlParameterSource().addValue("cr_id", cr_id),new BeanPropertyRowMapper<>(T_Base_Color.class));
			return data;
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}
	}
	@Override
	public T_Base_Color loadByName(String cr_name,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		
		sql.append(" SELECT cr_id,cr_code,cr_name,cr_spell,companyid");
		sql.append(" FROM t_base_color");
		sql.append(" WHERE cr_name  = :cr_name");
		sql.append(" AND companyid = :companyid");
		sql.append(" LIMIT 1");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(),
					new MapSqlParameterSource().addValue("cr_name", cr_name).addValue("companyid", companyid),
					new BeanPropertyRowMapper<>(T_Base_Color.class));
		} catch (Exception e) {
			return null;
		}
	}

	@Override
	public void save(T_Base_Color color) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" select f_three_code(max(cr_code+0)) from t_base_color ");
		sql.append(" where 1=1");
		sql.append(" and companyid=:companyid");
		String code = namedParameterJdbcTemplate.queryForObject(sql.toString(), new BeanPropertySqlParameterSource(color), String.class);
		color.setCr_code(code);
		sql.setLength(0);
		sql.append("INSERT INTO t_base_color");
		sql.append(" (cr_code,cr_spell,cr_name,companyid)");
		sql.append(" VALUES(:cr_code,:cr_spell,:cr_name,:companyid)");
		
		KeyHolder holder = new GeneratedKeyHolder(); 
		
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(color),holder);
		int id = holder.getKey().intValue();
		color.setCr_id(id);
	}

	@Override
	public void save(List<T_Base_Color> colors) {
		StringBuffer sql = new StringBuffer("");
		sql.append("INSERT INTO t_base_color");
		sql.append(" (cr_code,cr_spell,cr_name,companyid)");
		sql.append(" (SELECT f_three_code (max(cr_code + 0))as cr_code,:cr_spell,:cr_name,:companyid from t_base_color where 1=1 and companyid=:companyid)");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(colors.toArray()));
	}

	@Override
	public void update(T_Base_Color color) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" update t_base_color");
		sql.append(" set cr_spell=:cr_spell");
		sql.append(" ,cr_name=:cr_name");
		sql.append(" where cr_id=:cr_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(color));
	}

	@Override
	public void del(Integer cr_id) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_base_color");
		sql.append(" WHERE cr_id=:cr_id");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("cr_id", cr_id));
	}

	@Override
	public List<T_Base_Color> colorList(Map<String, Object> param) {
		Object name = param.get("name");
		String colorCodes = (String)param.get("colorCodes");
		StringBuffer sql = new StringBuffer("");
		sql.append(" select cr_id,cr_code,cr_name,cr_spell");
		sql.append(" from t_base_color t");
		sql.append(" where 1 = 1");
        if(null != name && !"".equals(name)){
        	sql.append(" AND (INSTR(t.cr_code,:name)>0 OR INSTR(t.cr_name,:name)>0 OR INSTR(t.cr_spell,:name)>0)");
        }
        // 主要作用，判断颜色是否已选择
     	if (colorCodes != null && !"".equals(colorCodes)) {
     		String[] arr = colorCodes.split(",");
     		sql.append(" AND cr_code NOT IN ("); 
     		for (String cr_code : arr) {
     			sql.append("'" + cr_code + "',");
     		}
     		sql.deleteCharAt(sql.length()-1);
     		sql.append(" )");
     	}
		sql.append(" and t.companyid=:companyid");
		sql.append(" order by cr_id asc");
		List<T_Base_Color> list = namedParameterJdbcTemplate.query(sql.toString(), param, new BeanPropertyRowMapper<>(T_Base_Color.class));
		return list;
	}

	@Override
	public List<T_Base_Color> queryByPdCode(Map<String, Object> param) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT cr_code,cr_name");
		sql.append(" FROM t_base_product_color t");
		sql.append(" JOIN t_base_color c");
		sql.append(" ON t.pdc_cr_code=c.cr_code");
		sql.append(" AND t.companyid=c.companyid");
		sql.append(" WHERE pdc_pd_code=:pd_code");
		sql.append(" AND t.companyid=:companyid");
		return namedParameterJdbcTemplate.query(sql.toString(), param, 
				new BeanPropertyRowMapper<>(T_Base_Color.class));
	}

	@Override
	public T_Base_Product_Color loadProductColor(String pd_code,String cr_code,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		
		sql.append(" SELECT pdc_id,pdc_pd_code,pdc_cr_code,companyid");
		sql.append(" FROM t_base_product_color");
		sql.append(" WHERE 1=1");
		sql.append(" AND pdc_pd_code = :pd_code");
		sql.append(" AND pdc_cr_code = :cr_code");
		sql.append(" AND companyid = :companyid");
		sql.append(" LIMIT 1");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(),
					new MapSqlParameterSource().addValue("pd_code", pd_code).addValue("cr_code", cr_code).addValue("companyid", companyid),
					new BeanPropertyRowMapper<>(T_Base_Product_Color.class));
		} catch (Exception e) {
			return null;
		}
	}
	
	@Override
	public void saveProductColor(T_Base_Product_Color productColor) {
		StringBuffer sql = new StringBuffer("");
		sql.append("INSERT INTO t_base_product_color");
		sql.append(" (pdc_pd_code,pdc_cr_code,companyid)");
		sql.append(" VALUES(:pdc_pd_code,:pdc_cr_code,:companyid)");
		KeyHolder holder = new GeneratedKeyHolder(); 
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(productColor),holder);
		productColor.setPdc_id(holder.getKey().intValue());
	}
}
